package cn.com.acca.ma.dao.impl;

import cn.com.acca.ma.pojo.OracleDatabaseObjectDefinition;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.sql.CLOB;
import org.hibernate.Query;
import org.hibernate.SQLQuery;

import cn.com.acca.ma.dao.ProjectDao;
import cn.com.acca.ma.hibernate.util.HibernateUtil;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.jdbc.Work;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;

import java.io.BufferedReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class ProjectDaoImpl extends BaseDaoImpl<ProjectDaoImpl> implements ProjectDao {

    /**
     * 收集数据库统计信息
     */
    @Override
    public void gatherDatabaseStatistics() {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        SQLQuery query = session.createSQLQuery("{call PKG_TOOL.GATHER_DATEBASE_STATISTICS()}");
        query.executeUpdate();
        session.getTransaction().commit();
        session.close();
    }

    /**
     * 查询数据库对象类型列表
     *
     * @return
     */
    @Override
    public List<String> findDatabaseTypeList() {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("SELECT distinct t.object_type FROM USER_OBJECTS t");
        List<String> list = query.list();
        session.getTransaction().commit();
        session.close();

        return list;
    }

    /**
     * 根据数据库对象类型，查询对象名称列表
     *
     * @param objectType
     * @return
     */
    @Override
    public List<String> findObjectNameListByObjectType(String objectType) {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = ? and OBJECT_NAME NOT LIKE 'SYS_IL%'");
        query.setParameter(0, objectType);
        List<String> list = query.list();
        session.getTransaction().commit();
        session.close();

        return list;
    }

    /**
     * 删除TYPE
     *
     * @param type
     */
    @Override
    public void deleteType(String type) {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("DROP TYPE " + type);
        query.executeUpdate();
        session.getTransaction().commit();
        session.close();
    }

    /**
     * 删除PACKAGE
     *
     * @param packageName
     */
    @Override
    public void deletePackage(String packageName) {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("DROP PACKAGE " + packageName);
        query.executeUpdate();
        session.getTransaction().commit();
        session.close();
    }

    /**
     * 删除SEQUENCE
     * @param sequence
     */
    @Override
    public void deleteSequence(String sequence) {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("DROP SEQUENCE " + sequence);
        query.executeUpdate();
        session.getTransaction().commit();
        session.close();
    }

    /**
     * 删除TABLE
     *
     * @param table
     */
    @Override
    public void deleteTable(String table) {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("DROP TABLE " + table + " purge");
        query.executeUpdate();
        session.getTransaction().commit();
        session.close();
    }

    /**
     * 删除FUNCTION
     *
     * @param function
     */
    @Override
    public void deleteFunction(String function) {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("DROP FUNCTION " + function);
        query.executeUpdate();
        session.getTransaction().commit();
        session.close();
    }

    /**
     * 删除PROCEDURE
     *
     * @param procedure
     */
    @Override
    public void deleteProcedure(String procedure) {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("DROP PROCEDURE " + procedure);
        query.executeUpdate();
        session.getTransaction().commit();
        session.close();
    }

    /**
     * 根据数据库对象类型和名称，查询定义
     *
     * @param objectType
     * @param objectName
     * @return
     */
    @Override
    public String findDefinitionByObjectTypeAndObjectName(String objectType, String objectName) throws SQLException {
        OracleDatabaseObjectDefinition definition = null;
        String result = null;
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        SQLQuery query = session.createSQLQuery("SELECT DBMS_METADATA.GET_DDL(?, ?) CONTENT FROM DUAL");
        query.setParameter(0, objectType);
        query.setParameter(1, objectName);
        definition = (OracleDatabaseObjectDefinition) query.addEntity(OracleDatabaseObjectDefinition.class).uniqueResult();
        session.getTransaction().commit();
        session.close();
        result = definition.getContent().getSubString(1, (int) definition.getContent().length());

        return result;
    }

    /**
     * 查询全部序列名称
     *
     * @return
     */
    @Override
    public List<String> findAllSequenceName() {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
        Query query = session.createSQLQuery("SELECT t.sequence_name FROM user_sequences t");
        List<String> list = query.list();
        session.getTransaction().commit();
        session.close();

        return list;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     */
    @Override
    public void executeSql(String sql) throws Exception {
        session = HibernateUtil.currentSession();
        session.beginTransaction();
//        SQLQuery query = session.createSQLQuery(sql);
//        query.executeUpdate();
        session.doWork(new Work() {
                           @Override
                           public void execute(Connection connection) throws SQLException {
                               DatabaseMetaData databaseMetadata = connection.getMetaData();
                               OracleConnection oracleConnection = (OracleConnection) databaseMetadata.getConnection();
                               OracleCallableStatement ocs = (OracleCallableStatement) oracleConnection.prepareCall(sql);
                               ocs.execute();
                               ocs.close();
                           }
                       });
        session.getTransaction().commit();
        session.flush();
        session.close();
    }
}
